import pandas as pd
import numpy as np
import pymysql
import psutil
import socket
import time
from datetime import datetime, timedelta
from sklearn.ensemble import IsolationForest
from concurrent.futures import ThreadPoolExecutor, as_completed
import warnings
warnings.filterwarnings('ignore')

class MySQLAnomalyDetector:
    def __init__(self, db_config=None):
        """初始化异常检测器，支持数据库连接异常检测
        
        Args:
            db_config: 数据库连接配置，格式为:
                {
                    "host": "数据库主机地址",
                    "user": "用户名",
                    "password": "密码",
                    "port": 3306 (默认)
                }
        """
        self.db_config = db_config or {}
        self.db_connection = None
        self.metrics_data = None  # 性能指标数据
        self.anomaly_data = None  # 异常数据
        self.anomaly_details = []  # 异常点详细信息
        self.version_info = None
        self.anomaly_patterns = []  # 异常模式
        self.risk_scan_results = []  # 风险扫描结果
        self.connection_issues = []  # 连接相关异常记录
        # 定义特征列表
        self.features = ['cpu_usage', 'memory_usage', 'connections', 'qps', 'slow_queries']
        
    def connect_to_database(self, retries=2, delay=3):
        """连接到MySQL数据库，增强异常捕获与分类
        
        Args:
            retries: 重试次数
            delay: 重试间隔(秒)
            
        Returns:
            bool: 连接是否成功
        """
        if not self.db_config.get('host'):
            self.connection_issues.append({
                'timestamp': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                'host': '未知',
                'port': self.db_config.get('port', 3306),
                'error_msg': '未提供数据库主机地址',
                'issue_type': '配置错误',
                'solution': '检查数据库配置信息',
                'severity': 'high'
            })
            return False
            
        # 尝试多次连接
        for attempt in range(retries + 1):
            try:
                self.db_connection = pymysql.connect(
                    host=self.db_config['host'],
                    user=self.db_config.get('user', 'root'),
                    password=self.db_config.get('password', ''),
                    port=self.db_config.get('port', 3306),
                    charset='utf8mb4',
                    connect_timeout=10
                )
                print(f"成功连接到数据库: {self.db_config['host']}:{self.db_config.get('port', 3306)}")
                # 清除连接异常记录
                self.connection_issues = []
                return True
                
            except pymysql.OperationalError as e:
                error_code = e.args[0]
                error_msg = e.args[1]
                issue_type = ""
                solution = ""
                
                # 分类连接错误类型
                if error_code in (10061, 113, 61, 10060):  # 连接被拒绝或网络错误
                    issue_type = "端口连接异常"
                    solution = "检查数据库服务是否启动、3306端口是否开放、防火墙设置是否允许连接"
                elif error_code == 1045:  # 密码错误
                    issue_type = "用户认证失败"
                    solution = "检查用户名和密码是否正确，以及用户是否有权限访问该数据库"
                elif error_code == 2003:  # 无法连接到服务器
                    issue_type = "服务器不可达"
                    solution = "检查主机地址是否正确、网络是否通畅、数据库服务是否运行"
                elif error_code == 2013:  # 连接超时
                    issue_type = "连接超时"
                    solution = "检查网络稳定性、服务器负载及连接超时设置"
                else:
                    issue_type = "操作错误"
                    solution = f"查看错误代码 {error_code} 进行排查"
                
                # 记录连接异常
                self.connection_issues.append({
                    'timestamp': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                    'host': self.db_config['host'],
                    'port': self.db_config.get('port', 3306),
                    'error_code': error_code,
                    'error_msg': str(error_msg),
                    'issue_type': issue_type,
                    'solution': solution,
                    'severity': 'high' if error_code in (10061, 1045, 2003) else 'medium'
                })
                
                # 如果不是最后一次尝试，等待后重试
                if attempt < retries:
                    print(f"连接尝试 {attempt + 1} 失败，将在 {delay} 秒后重试...")
                    time.sleep(delay)
                else:
                    print(f"数据库连接失败 {self.db_config['host']}:{self.db_config.get('port', 3306)}: {issue_type} ({error_code}): {error_msg}")
                    return False
                    
            except Exception as e:
                self.connection_issues.append({
                    'timestamp': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                    'host': self.db_config['host'],
                    'port': self.db_config.get('port', 3306),
                    'error_msg': str(e),
                    'issue_type': '未知错误',
                    'solution': '检查数据库服务状态及配置',
                    'severity': 'medium'
                })
                
                if attempt < retries:
                    print(f"连接尝试 {attempt + 1} 失败，将在 {delay} 秒后重试...")
                    time.sleep(delay)
                else:
                    print(f"数据库连接失败 {self.db_config['host']}: {str(e)}")
                    return False
    
    def check_port_status(self, host, port=3306, timeout=5):
        """检查数据库端口是否可连接
        
        Args:
            host: 主机地址
            port: 端口号
            timeout: 超时时间(秒)
            
        Returns:
            dict: 包含检查结果的字典
        """
        try:
            with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s:
                s.settimeout(timeout)
                result = s.connect_ex((host, port))
                if result == 0:
                    return {
                        'status': 'open',
                        'message': f"端口 {host}:{port} 可连接",
                        'timestamp': datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                    }
                else:
                    return {
                        'status': 'closed',
                        'message': f"端口 {host}:{port} 不可连接",
                        'timestamp': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                        'severity': 'high'
                    }
        except Exception as e:
            return {
                'status': 'error',
                'message': f"检查端口 {host}:{port} 时出错: {str(e)}",
                'timestamp': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                'severity': 'medium'
            }
    
    def collect_metrics(self, duration_minutes=60):
        """
        收集性能指标数据，增强连接异常处理
        
        Args:
            duration_minutes: 收集数据的时长(分钟)
        """
        # 先检查端口状态
        port_check = self.check_port_status(
            self.db_config.get('host', 'localhost'),
            self.db_config.get('port', 3306)
        )
        
        if port_check['status'] != 'open':
            self.connection_issues.append({
                'timestamp': port_check['timestamp'],
                'host': self.db_config.get('host', 'localhost'),
                'port': self.db_config.get('port', 3306),
                'error_msg': port_check['message'],
                'issue_type': '端口连接异常',
                'solution': '检查数据库服务是否启动、端口是否开放、防火墙设置',
                'severity': port_check.get('severity', 'high')
            })
        
        # 尝试连接数据库
        connect_success = self.connect_to_database()
        if not connect_success:
            print("由于连接问题，仅收集系统级指标")
            self._collect_system_metrics(duration_minutes)
            return False
        
        try:
            cursor = self.db_connection.cursor()
            
            # 获取数据库版本信息
            cursor.execute("SELECT VERSION()")
            self.version_info = cursor.fetchone()[0]
            
            # 收集全局状态信息作为基准
            cursor.execute("SHOW GLOBAL STATUS")
            status_data = cursor.fetchall()
            status_dict = {item[0]: item[1] for item in status_data}
            
            # 初始化指标收集列表
            timestamps = []
            cpu_usage = []
            memory_usage = []
            connections = []
            qps = []
            slow_queries = []
            
            # 收集当前时间点及历史数据
            current_time = datetime.now()
            start_time = current_time - timedelta(minutes=duration_minutes - 1)
            
            # 收集当前系统指标
            for i in range(duration_minutes):
                record_time = start_time + timedelta(minutes=i)
                timestamps.append(record_time)
                
                # 系统指标
                cpu_usage.append(psutil.cpu_percent(interval=0.1))
                memory_usage.append(psutil.virtual_memory().percent)
                
                # 数据库指标
                try:
                    # 检查连接是否仍然有效
                    cursor.execute("SELECT 1")
                    
                    # 收集连接数
                    cursor.execute("SHOW GLOBAL STATUS LIKE 'Threads_connected'")
                    conn_count = int(cursor.fetchone()[1])
                    connections.append(conn_count)
                    
                    # 收集QPS (每秒查询数)
                    cursor.execute("SHOW GLOBAL STATUS LIKE 'Queries'")
                    queries = int(cursor.fetchone()[1])
                    if i > 0:
                        qps_val = max(0, queries - prev_queries)
                    else:
                        qps_val = 0
                    qps.append(qps_val)
                    prev_queries = queries
                    
                    # 收集慢查询数
                    cursor.execute("SHOW GLOBAL STATUS LIKE 'Slow_queries'")
                    slow_count = int(cursor.fetchone()[1])
                    slow_queries.append(slow_count)
                    
                    # 短时间休眠，避免过度占用资源
                    if i < duration_minutes - 1:
                        time.sleep(1)
                        
                except Exception as e:
                    # 数据库连接中断，记录异常
                    connections.append(0)
                    qps.append(0)
                    slow_queries.append(0)
                    
                    self.connection_issues.append({
                        'timestamp': record_time.strftime('%Y-%m-%d %H:%M:%S'),
                        'host': self.db_config['host'],
                        'port': self.db_config.get('port', 3306),
                        'error_msg': f"收集指标时连接中断: {str(e)}",
                        'issue_type': '连接中断',
                        'solution': '检查网络稳定性和数据库服务状态',
                        'severity': 'high'
                    })
            
            # 创建指标数据DataFrame
            self.metrics_data = pd.DataFrame({
                'timestamp': timestamps,
                'cpu_usage': cpu_usage,
                'memory_usage': memory_usage,
                'connections': connections,
                'qps': qps,
                'slow_queries': slow_queries,
                'host': self.db_config['host']
            })
            
            print("性能指标收集完成")
            return True
            
        except Exception as e:
            print(f"收集性能指标失败: {str(e)}")
            self._collect_system_metrics(duration_minutes)
            return False
    
    def _collect_system_metrics(self, duration_minutes):
        """当数据库连接失败时，收集系统级指标"""
        timestamps = pd.date_range(end=datetime.now(), periods=duration_minutes, freq='T')
        
        # 收集系统CPU和内存使用情况
        cpu_usage = []
        memory_usage = []
        
        for _ in range(duration_minutes):
            cpu_usage.append(psutil.cpu_percent(interval=0.1))
            memory_usage.append(psutil.virtual_memory().percent)
            if _ < duration_minutes - 1:
                time.sleep(1)
        
        # 创建指标数据DataFrame
        host_name = self.db_config.get('host', socket.gethostname())
        self.metrics_data = pd.DataFrame({
            'timestamp': timestamps,
            'cpu_usage': cpu_usage,
            'memory_usage': memory_usage,
            'connections': [0] * duration_minutes,
            'qps': [0] * duration_minutes,
            'slow_queries': [0] * duration_minutes,
            'host': host_name
        })
        print(f"系统级指标收集完成 (主机: {host_name})")
    
    def _analyze_anomaly_causes(self, row):
        """分析异常点的可能原因，包含连接异常分析"""
        if self.metrics_data is None:
            return "无法分析异常原因：无性能指标数据"
            
        # 先检查是否有连接异常
        if self.connection_issues:
            conn_issues = "\n".join([
                f"- {issue['timestamp']}: {issue['issue_type']} (解决方案: {issue['solution']})" 
                for issue in self.connection_issues[:3]
            ])
            result = f"检测到数据库连接异常:\n{conn_issues}\n\n"
        else:
            result = ""
        
        # 计算正常范围（均值±2倍标准差）
        metrics_stats = {
            'cpu_usage': {
                'mean': self.metrics_data['cpu_usage'].mean(),
                'std': self.metrics_data['cpu_usage'].std(),
                'threshold_high': 90,  # 高阈值
                'threshold_low': 5     # 低阈值
            },
            'memory_usage': {
                'mean': self.metrics_data['memory_usage'].mean(),
                'std': self.metrics_data['memory_usage'].std(),
                'threshold_high': 90,
                'threshold_low': 20
            },
            'connections': {
                'mean': self.metrics_data['connections'].mean(),
                'std': self.metrics_data['connections'].std(),
                'threshold_high': 160,  # 默认阈值
                'threshold_low': 5
            },
            'qps': {
                'mean': self.metrics_data['qps'].mean(),
                'std': self.metrics_data['qps'].std(),
                'threshold_high': self.metrics_data['qps'].mean() * 2 if self.metrics_data['qps'].mean() > 0 else 400,
                'threshold_low': 10
            },
            'slow_queries': {
                'mean': self.metrics_data['slow_queries'].mean(),
                'std': self.metrics_data['slow_queries'].std(),
                'threshold_high': 10,
                'threshold_low': 0
            }
        }
        
        causes = []
        abnormal_metrics = []
        
        # 检查每个指标是否异常
        for metric in self.features:
            value = row[metric]
            mean = metrics_stats[metric]['mean']
            std = metrics_stats[metric]['std']
            high_threshold = metrics_stats[metric]['threshold_high']
            low_threshold = metrics_stats[metric]['threshold_low']
            
            # 判断是否超出正常范围
            is_high = value > high_threshold or value > mean + 2 * std
            is_low = value < low_threshold or value < mean - 2 * std
            
            if is_high or is_low:
                direction = "过高" if is_high else "过低"
                normal_range = f"{mean - 2*std:.2f}-{mean + 2*std:.2f}"
                abnormal_metrics.append(f"{metric.replace('_', ' ')} {direction} (值: {value:.2f}, 正常范围: {normal_range})")
                
                # 根据指标类型分析可能原因
                if metric == 'cpu_usage' and is_high:
                    causes.append("可能存在CPU密集型查询或大量并发查询")
                elif metric == 'memory_usage' and is_high:
                    causes.append("InnoDB缓冲池配置过大或内存泄漏")
                elif metric == 'connections' and is_high:
                    causes.append("应用程序连接未正确关闭或连接池配置不合理")
                elif metric == 'connections' and is_low and self.connection_issues:
                    causes.append("连接数过低，可能与数据库连接异常有关")
                elif metric == 'qps' and is_high:
                    causes.append("应用程序请求量突增或缓存失效")
                elif metric == 'qps' and is_low:
                    causes.append("应用程序故障或网络问题")
                elif metric == 'slow_queries' and is_high:
                    causes.append("存在未优化的SQL语句或索引缺失")
        
        # 汇总异常指标和可能原因
        result += "异常指标: " + "; ".join(abnormal_metrics) + "\n\n可能原因:\n"
        if causes:
            for i, cause in enumerate(causes, 1):
                result += f"- {i}. {cause}\n"
        else:
            result += "未明确识别的异常模式，建议进一步检查系统日志和数据库状态"
            
        return result
    
    def detect_anomalies(self):
        """使用孤立森林算法检测异常值，包含连接异常"""
        # 先检查是否有连接异常
        if self.connection_issues:
            print(f"检测到 {len(self.connection_issues)} 个连接相关异常")
        
        if self.metrics_data is None:
            print("没有可用的指标数据，无法检测异常")
            return False
            
        # 准备用于异常检测的数据
        X = self.metrics_data[self.features].values
        
        # 训练孤立森林模型
        model = IsolationForest(n_estimators=100, contamination=0.05, random_state=42)
        self.metrics_data['anomaly'] = model.fit_predict(X)
        
        # 将异常标记为1，正常为0
        self.metrics_data['anomaly'] = self.metrics_data['anomaly'].map({1: 0, -1: 1})
        
        # 提取异常数据
        self.anomaly_data = self.metrics_data[self.metrics_data['anomaly'] == 1]
        
        # 分析每个异常点的原因并提取异常模式
        self.anomaly_details = []
        self.anomaly_patterns = []
        
        # 添加连接异常到异常详情
        for issue in self.connection_issues:
            self.anomaly_details.append({
                'timestamp': issue['timestamp'],
                'host': issue['host'],
                'analysis': f"连接异常: {issue['issue_type']} - {issue['error_msg']}\n建议解决方案: {issue['solution']}",
                'metrics': {'connection_status': 'failed'},
                'severity': issue['severity'],
                'possible_causes': [issue['error_msg']],
                'temporary_fix': issue['solution']
            })
            
            self.anomaly_patterns.append({
                'pattern_type': 'connection_issue',
                'description': issue['issue_type'],
                'risk_level': issue['severity'],
                'common_causes': [issue['error_msg']]
            })
        
        # 分析性能指标异常
        for _, row in self.anomaly_data.iterrows():
            cause_analysis = self._analyze_anomaly_causes(row)
            self.anomaly_details.append({
                'timestamp': row['timestamp'],
                'host': row['host'],
                'analysis': cause_analysis,
                'metrics': row[self.features].to_dict(),
                'possible_causes': self._extract_possible_causes(row),
                'temporary_fix': self._suggest_temporary_fix(row)
            })
            
            # 提取异常模式
            patterns = self._extract_anomaly_pattern(row)
            if patterns:
                self.anomaly_patterns.extend(patterns)
        
        total_anomalies = len(self.anomaly_data) + len(self.connection_issues)
        print(f"异常检测完成，共发现 {total_anomalies} 个异常 (含 {len(self.connection_issues)} 个连接异常)")
        return True
    
    def _extract_possible_causes(self, row):
        """提取异常可能的原因"""
        causes = []
        if row['connections'] == 0 and any(issue['host'] == row['host'] for issue in self.connection_issues):
            causes.append("数据库连接异常导致无法获取有效连接")
        if row['cpu_usage'] > 90:
            causes.append("CPU使用率过高，可能存在资源密集型查询")
        if row['memory_usage'] > 90:
            causes.append("内存使用率过高，可能存在内存泄漏或配置不当")
        if row['connections'] > 160:
            causes.append("连接数过多，可能存在连接池配置问题或连接未释放")
        if row['slow_queries'] > 10:
            causes.append("慢查询数量过多，可能存在未优化的SQL")
        return causes if causes else ["需要进一步分析系统日志和数据库状态"]
    
    def _suggest_temporary_fix(self, row):
        """根据异常情况提供临时解决方案"""
        fixes = []
        if any(issue['host'] == row['host'] for issue in self.connection_issues):
            fixes.append("检查数据库服务状态，尝试重启服务并验证网络连接")
        if row['cpu_usage'] > 90:
            fixes.append("终止长时间运行的查询，检查并优化CPU密集型操作")
        if row['memory_usage'] > 90:
            fixes.append("检查内存使用情况，释放不必要的缓存，考虑增加系统内存")
        if row['connections'] > 160:
            fixes.append("调整连接池配置，增加wait_timeout值，检查应用是否正确释放连接")
        return "\n".join(fixes) if fixes else "建议先解决根本原因，再采取相应措施"
    
    def _extract_anomaly_pattern(self, row):
        """提取异常模式"""
        patterns = []
        
        metrics_stats = {
            'cpu_usage': {'threshold_high': 90, 'threshold_low': 5},
            'memory_usage': {'threshold_high': 90, 'threshold_low': 20},
            'connections': {'threshold_high': 160, 'threshold_low': 5},
            'qps': {'threshold_high': (row['qps'] * 2) if row['qps'] > 0 else 400, 'threshold_low': 10},
            'slow_queries': {'threshold_high': 10, 'threshold_low': 0}
        }
        
        # 检查每个指标的异常情况
        if row['cpu_usage'] > metrics_stats['cpu_usage']['threshold_high']:
            patterns.append({
                'pattern_type': 'high_cpu',
                'description': f"CPU使用率过高 ({row['cpu_usage']:.1f}%)",
                'risk_level': 'high',
                'common_causes': ['CPU密集型查询', '并发连接过多', '系统资源不足']
            })
        elif row['cpu_usage'] < metrics_stats['cpu_usage']['threshold_low']:
            patterns.append({
                'pattern_type': 'low_cpu',
                'description': f"CPU使用率过低 ({row['cpu_usage']:.1f}%)",
                'risk_level': 'low',
                'common_causes': ['系统负载过低', '应用程序异常']
            })
            
        if row['memory_usage'] > metrics_stats['memory_usage']['threshold_high']:
            patterns.append({
                'pattern_type': 'high_memory',
                'description': f"内存使用率过高 ({row['memory_usage']:.1f}%)",
                'risk_level': 'high',
                'common_causes': ['内存泄漏', '缓冲池配置过大', '大查询占用内存']
            })
        elif row['memory_usage'] < metrics_stats['memory_usage']['threshold_low']:
            patterns.append({
                'pattern_type': 'low_memory',
                'description': f"内存使用率过低 ({row['memory_usage']:.1f}%)",
                'risk_level': 'low',
                'common_causes': ['内存配置过高未充分利用']
            })
            
        if row['connections'] > metrics_stats['connections']['threshold_high']:
            patterns.append({
                'pattern_type': 'high_connections',
                'description': f"数据库连接数过高 ({row['connections']})",
                'risk_level': 'medium',
                'common_causes': ['连接池配置不合理', '应用未正确释放连接', '连接泄漏']
            })
        elif row['connections'] < metrics_stats['connections']['threshold_low'] and row['connections'] > 0:
            patterns.append({
                'pattern_type': 'low_connections',
                'description': f"数据库连接数过低 ({row['connections']})",
                'risk_level': 'low',
                'common_causes': ['应用访问量低', '连接限制设置过低']
            })
            
        if row['qps'] > metrics_stats['qps']['threshold_high']:
            patterns.append({
                'pattern_type': 'high_qps',
                'description': f"查询频率过高 ({row['qps']} QPS)",
                'risk_level': 'medium',
                'common_causes': ['应用请求突增', '缓存失效', '查询未优化']
            })
        elif row['qps'] < metrics_stats['qps']['threshold_low'] and row['qps'] > 0:
            patterns.append({
                'pattern_type': 'low_qps',
                'description': f"查询频率过低 ({row['qps']} QPS)",
                'risk_level': 'low',
                'common_causes': ['应用访问量低', '应用异常']
            })
            
        if row['slow_queries'] > metrics_stats['slow_queries']['threshold_high']:
            patterns.append({
                'pattern_type': 'high_slow_queries',
                'description': f"慢查询数量过多 ({row['slow_queries']})",
                'risk_level': 'medium',
                'common_causes': ['SQL语句未优化', '缺少索引', '表结构不合理']
            })
            
        return patterns
    
    def scan_remote_servers(self, servers, timeout=10):
        """
        扫描远程服务器的MySQL异常情况
        
        Args:
            servers: 服务器列表，每个元素为数据库配置字典
            timeout: 超时时间(秒)
            
        Returns:
            list: 扫描结果
        """
        results = []
        
        with ThreadPoolExecutor(max_workers=5) as executor:
            # 为每个服务器创建一个检测任务
            futures = {
                executor.submit(self._scan_single_server, server, timeout): server
                for server in servers
            }
            
            for future in as_completed(futures):
                server = futures[future]
                try:
                    result = future.result()
                    results.append(result)
                except Exception as e:
                    results.append({
                        'host': server.get('host', 'unknown'),
                        'port': server.get('port', 3306),
                        'status': 'error',
                        'error': str(e),
                        'timestamp': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                        'risk_level': 'high',
                        'anomaly_patterns': []
                    })
        
        self.risk_scan_results = results
        return results
    
    def _scan_single_server(self, server_config, timeout):
        """扫描单个服务器"""
        start_time = datetime.now()
        scanner = MySQLAnomalyDetector(server_config)
        
        # 检查端口状态
        port_check = scanner.check_port_status(
            server_config['host'],
            server_config.get('port', 3306),
            timeout
        )
        
        # 尝试连接数据库
        connect_success = scanner.connect_to_database(retries=1, delay=2)
        
        # 收集简要指标
        if connect_success:
            scanner.collect_metrics(duration_minutes=2)  # 短时间收集
            scanner.detect_anomalies()
            
            risk_level = 'high' if len(scanner.anomaly_details) > 2 else \
                         'medium' if len(scanner.anomaly_details) > 0 else 'low'
                         
            return {
                'host': server_config['host'],
                'port': server_config.get('port', 3306),
                'status': 'accessible',
                'version': scanner.version_info,
                'timestamp': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                'risk_level': risk_level,
                'anomaly_count': len(scanner.anomaly_details),
                'anomaly_patterns': scanner.anomaly_patterns[:5]  # 限制返回数量
            }
        else:
            # 连接失败，使用连接异常信息
            return {
                'host': server_config['host'],
                'port': server_config.get('port', 3306),
                'status': 'inaccessible',
                'port_check': port_check,
                'timestamp': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                'risk_level': 'high',
                'anomaly_count': len(scanner.connection_issues),
                'anomaly_patterns': [
                    {
                        'pattern_type': 'connection_issue',
                        'description': issue['issue_type'],
                        'risk_level': issue['severity'],
                        'common_causes': [issue['error_msg']]
                    } 
                    for issue in scanner.connection_issues[:3]
                ]
            }
    
    def close(self):
        """关闭数据库连接"""
        if self.db_connection:
            try:
                self.db_connection.close()
                print("数据库连接已关闭")
            except Exception as e:
                print(f"关闭数据库连接时出错: {str(e)}")
    